"""
Case Type   : 基础功能
Case Name   : 创建索引,开启bypass,查询执行计划
Create At   : 2022/07/25
Owner       : liu-tong-8848
Description :
    1.配置bypass参数
    2.创建表
    3.创建索引
    4.插入数据
    5.查询计划
    6.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.返回查询计划
    6.成功
History     :
"""
import os
import unittest
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DdlTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.com = Common()
        self.constant = Constant()
        self.comshpri = CommonSH('PrimaryDbUser')
        self.tb_name1 = "t_index_bypass_0256_01"
        self.tb_name2 = "t_index_bypass_0256_02"
        self.index = "i_bypass_0256"

    def test_index_bypass(self):
        self.log.info(f'----{os.path.basename(__file__)} start----')
        text = '----step1: 配置bypass参数 expect:成功-----'
        self.log.info(text)
        result1 = self.comshpri.execut_db_sql("show enable_bitmapscan")
        self.log.info(result1)
        result2 = self.comshpri.execut_db_sql("show enable_seqscan")
        self.log.info(result2)
        result3 = self.comshpri.execut_db_sql("show enable_opfusion")
        self.log.info(result3)
        self.enable_bitmapscan = str(result1.strip().splitlines()[-2]).strip()
        self.enable_seqscan = str(result2.strip().splitlines()[-2]).strip()
        self.enable_opfusion = str(result3.strip().splitlines()[-2]).strip()
        if self.enable_bitmapscan != 'off':
            result = self.comshpri.execute_gsguc(
                "reload", self.constant.GSGUC_SUCCESS_MSG,
                "enable_bitmapscan = off")
            self.log.info(result)
            self.assertTrue(result, '执行失败' + text)
        if self.enable_seqscan != 'off':
            result = self.comshpri.execute_gsguc(
                "reload", self.constant.GSGUC_SUCCESS_MSG,
                "enable_seqscan = off")
            self.log.info(result)
            self.assertTrue(result, '执行失败' + text)
        if self.enable_opfusion != 'on':
            result = self.comshpri.execute_gsguc(
                "reload", self.constant.GSGUC_SUCCESS_MSG,
                "enable_opfusion = on")
            self.log.info(result)
            self.assertTrue(result, '执行失败' + text)

        text = '----step2:创建表 expect:成功----'
        self.log.info(text)
        create_table = self.comshpri.execut_db_sql(
            f'''drop table if exists {self.tb_name1};
            drop table if exists {self.tb_name2};
            create table {self.tb_name1}(id int,class int,name text,score 
            float);
            create table {self.tb_name2}(c1 int primary key, c2 int, c3 int);
            ''')
        self.log.info(create_table)
        self.assertEqual(create_table.count(self.constant.TABLE_DROP_SUCCESS),
                         2, '执行失败' + text)
        self.assertEqual(
            create_table.count(self.constant.TABLE_CREATE_SUCCESS), 3,
            '执行失败' + text)

        text = '----step3: 创建索引 expect:成功----'
        self.log.info(text)
        create_index = self.comshpri.execut_db_sql(
            f'''drop index if exists {self.index};
            create index {self.index} on {self.tb_name1}(class);''')
        self.log.info(create_index)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, create_index,
                      '执行失败' + text)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, create_index,
                      '执行失败' + text)

        text = '----step4: 插入数据 expect:成功----'
        self.log.info(text)
        insert_data = self.comshpri.execut_db_sql(
            f'''insert into {self.tb_name1} values (1,2,'name',4.5),
            (2,2,'name',4.5),(1,3,'name',4.5),(1,4,'named',4.5),
            (1,2,'namde',6.5),(1,2,'name',4.5);
            insert into {self.tb_name2} select i,i,i from 
            generate_series(1,100) i;''')
        self.log.info(insert_data)
        self.assertEqual(insert_data.count(self.constant.INSERT_SUCCESS_MSG),
                         2, '执行失败' + text)

        text = '----step5: 查询计划 expect:返回查询计划----'
        self.log.info(text)
        explain_plan1 = self.comshpri.execut_db_sql(
            f'''explain select name from {self.tb_name1} where class=3;''')
        self.log.info(explain_plan1)
        explain_plan2 = self.comshpri.execut_db_sql(
            f'''explain select id from {self.tb_name1} where class=3;''')
        self.log.info(explain_plan2)
        explain_plan3 = self.comshpri.execut_db_sql(
            f'''explain select name from {self.tb_name1} where class=2 offset 
            1 limit 10;''')
        self.log.info(explain_plan3)
        explain_plan4 = self.comshpri.execut_db_sql(
            f'''explain select * from {self.tb_name2} where c1>1 limit 2;''')
        self.log.info(explain_plan4)
        self.assertIn('[Bypass]', explain_plan1, '执行失败' + text)
        self.assertIn('Index Cond: (class = 3)', explain_plan1, '执行失败' +
                      text)
        self.assertIn('[Bypass]', explain_plan2, '执行失败' + text)
        self.assertIn('Index Cond: (class = 3)', explain_plan2, '执行失败' +
                      text)
        self.assertIn('[Bypass]', explain_plan3, '执行失败' + text)
        self.assertIn(f'Index Scan using {self.index} on {self.tb_name1}',
                      explain_plan3, '执行失败' + text)
        self.assertIn('[Bypass]', explain_plan4, '执行失败' + text)
        self.assertIn(f'Index Scan using {self.tb_name2}_pkey on '
                      f'{self.tb_name2}', explain_plan4, '执行失败' + text)

    def tearDown(self):
        text = "----step6:环境清理 expect:成功----"
        self.log.info(text)
        clean_eviro = self.comshpri.execut_db_sql(
            f'''drop table {self.tb_name1} cascade;
            drop table {self.tb_name2};''')
        self.log.info(clean_eviro)
        self.assertEqual(clean_eviro.count(self.constant.TABLE_DROP_SUCCESS),
                         2, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
